package com.suning.framework;

import org.apache.commons.lang.ObjectUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

/**
 * 〈一句话功能简述〉<br>
 * 〈功能详细描述〉
 *
 * @author 16080951(林健)
 * @Copyright (c) 2016 苏宁软件技术有限公司 版权所有
 * @see [相关类/方法]（可选）
 * @since [产品/模块版本] （可选）
 */
public class Dao extends JdbcDaoSupport {


    public List<Mps_member_portrait_info> query() {

        String sql = "\n"
                + "SELECT\n"
                + "  page_row_.*\n"
                + "FROM(\n"
                + "      SELECT\n"
                + "        ROWNUMBER() OVER() AS rowid_,orgin_tbl_.*\n"
                + "      FROM(\n"
                + "            SELECT  * from FDM_APP.MPS_MEMBER_PORTRAIT_INFO\n"
                + "          ) orgin_tbl_) page_row_\n"
                + "WHERE\n"
                + "  page_row_.rowid_ >= (100 + 1)\n"
                + "  AND page_row_.rowid_ <= (0 + 1000)\n";
        List<Mps_member_portrait_info> result = this.getJdbcTemplate()
                .query(sql, new RowMapper<Mps_member_portrait_info>() {
                    public Mps_member_portrait_info mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Mps_member_portrait_info obj = new Mps_member_portrait_info();
                        obj.setAcct_no(rs.getString("acct_no"));
                        obj.setActv_lvl(rs.getString("actv_lvl"));
                        obj.setAge_type(rs.getString("age_type"));
                        obj.setName(rs.getString("name"));
                        return obj;
                    }
                });
        return result;

    }

    /**
     * 获取会员画像总量
     * @return
     */
    public Long getCount() {

        String sql ="SELECT count(1) as r from FDM_APP.MPS_MEMBER_PORTRAIT_INFO ";
        return this.getJdbcTemplate().queryForLong(sql);
    }

    /**
     * 分页查询会员画像数据
     * @return
     */
    public List<Mps_member_portrait_info> queryForPage(int start,int end) {

        String sql = "\n"
                + "SELECT\n"
                + "  page_row_.*\n"
                + "FROM(\n"
                + "      SELECT\n"
                + "        ROWNUMBER() OVER() AS rowid_,orgin_tbl_.*\n"
                + "      FROM(\n"
                + "            SELECT  * from FDM_APP.MPS_MEMBER_PORTRAIT_INFO\n"
                + "          ) orgin_tbl_) page_row_\n"
                + "WHERE\n"
                + "  page_row_.rowid_ > ? \n"
                + "  AND page_row_.rowid_ <=  ? \n";
        List<Mps_member_portrait_info> result = this.getJdbcTemplate()
                .query(sql, new Object[]{new Integer(start),new Integer(end)},new RowMapper<Mps_member_portrait_info>() {
                    public Mps_member_portrait_info mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Mps_member_portrait_info obj = new Mps_member_portrait_info();
                        obj.setAcct_no(rs.getString("acct_no"));
                        obj.setActv_lvl(rs.getString("actv_lvl"));
                        obj.setAge_type(rs.getString("age_type"));
                        obj.setName(rs.getString("name"));
                        obj.setAcct_no(rs.getString("acct_no"));
                        obj.setMember_id(rs.getString("member_id"));
                        obj.setName(rs.getString("name"));
                        obj.setSex(rs.getString("sex"));
                        obj.setAge_type(rs.getString("age_type"));
                        obj.setMbl(rs.getString("mbl"));
                        obj.setId_card(rs.getString("id_card"));
                        obj.setEmail(rs.getString("email"));
                        obj.setMgt_area(rs.getString("mgt_area"));
                        obj.setMgt_org(rs.getString("mgt_org"));
                        obj.setMgt_store(rs.getString("mgt_store"));
                        obj.setIs_has_car(rs.getString("is_has_car"));
                        obj.setIs_has_house(rs.getString("is_has_house"));
                        obj.setIs_baby(rs.getString("is_baby"));
                        obj.setIncome(rs.getString("income"));
                        obj.setBuy_cpy(rs.getString("buy_cpy"));
                        obj.setLifecycle(rs.getString("lifecycle"));
                        obj.setInv_prd_love(rs.getString("inv_prd_love"));
                        obj.setInv_risk_love(rs.getString("inv_risk_love"));
                        obj.setInv_term_love(rs.getString("inv_term_love"));
                        obj.setEg_mem_lvl(rs.getString("EG_MEM_LVL"));
                        obj.setFin_rgst_dt(rs.getString("fin_rgst_dt"));
                        obj.setFin_pay_tm_1st(rs.getString("fin_pay_tm_1st"));
                        obj.setFin_hold_amt(rs.getDouble("fin_hold_amt"));
                        obj.setBof_hold_amt(rs.getDouble("bof_hold_amt"));
                        obj.setBil_hold_amt(rs.getDouble("bil_hold_amt"));
                        obj.setFnd_hold_amt(rs.getDouble("fnd_hold_amt"));
                        obj.setGds_cgy_love(rs.getString("gds_cgy_love"));
                        obj.setDebt_st(rs.getString("debt_st"));
                        obj.setActv_lvl(rs.getString("actv_lvl"));
                        obj.setLoyal_lvl(rs.getString("loyal_lvl"));
                        obj.setValue_lvl(rs.getString("value_lvl"));
                        if(!ObjectUtils.defaultIfNull(rs.getDate("birth_dt"),"").equals("")){
                            obj.setBirth_dt(new Date(rs.getDate("birth_dt").getTime()));
                        }
                        obj.setLongpositionname(rs.getString("longpositionname"));
                        obj.setIs_inr_emp(rs.getString("is_inr_emp"));
                        obj.setOrgname(rs.getString("orgname"));
                        return obj;
                    }
                });
        return result;

    }


}
